//
//  dbFunctions.m
//  smartRepair
//
//  Created by pankaj sachdeva on 07/08/11.
//  Copyright 2011 __MyCompanyName__. All rights reserved.
//

#import "dbFunctions.h"

static sqlite3 *database = nil;
static sqlite3_stmt *addStmt = nil;
static sqlite3_stmt *deleteStmt = nil;

@implementation dbFunctions

- (void)createEditableCopyOfDatabaseIfNeeded {
    // First, test for existence.
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"autosoftCollection.sqlite"];
    success = [fileManager fileExistsAtPath:writableDBPath];
    if (success) return;
    // The writable database does not exist, so copy the default to the appropriate location.
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"autosoftCollection.sqlite"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    if (!success) {
        NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    }
}

- (NSString *)GetDBPath {
	if(path==nil) {
		[self createEditableCopyOfDatabaseIfNeeded];
		NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
		NSString *documentsDirectory = [paths objectAtIndex:0];
		path = [documentsDirectory stringByAppendingPathComponent:@"autosoftCollection.sqlite"];
	}
	return path;
}

-(NSMutableArray *)getPDF {
    NSMutableArray *arr = [[NSMutableArray alloc] init];
	if(sqlite3_open([[self GetDBPath] UTF8String], &database) == SQLITE_OK) {
		NSLog(@"%@",path);
		const char *sql = "select itemName from carSection";
        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
            while (sqlite3_step(statement) == SQLITE_ROW) {
				//NSMutableDictionary *dict = [[NSMutableDictionary alloc] init];
				
				if([[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)] length]>0)
					[arr addObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)]];
				 
				
				
            }
        }
		else {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
		}
        
        sqlite3_finalize(statement);
    } else {
        sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
	NSLog(@"Item Name%@",arr);
	return arr;
}


-(void)addNewSection:(NSString*)carSection  accidentDamage:(NSString*)damage nonRelatedAccident:(NSString*)nonrelated  privateQArea:(NSString*)qArea 
{
    if(sqlite3_open([[self GetDBPath] UTF8String], &database) == SQLITE_OK) {
		
        NSString *str1 = [[NSString alloc] initWithString:carSection];
        NSString *str2 = [[NSString alloc] initWithString:damage];

		NSString *str3 = [[NSString alloc] initWithString:nonrelated];
        NSString *str4 = [[NSString alloc] initWithString:qArea];

        NSLog(@"%@",path);
        const char *sql = "insert into NewCarSection(carsection, accidentdamage, nonrelatedaccident, privatequotearea) values(?,?,?,?)";
        sqlite3_stmt *stmt;
        int success;
        if (sqlite3_prepare_v2(database, sql, -1, &stmt, NULL) != SQLITE_OK) {
            NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
        }
        else {
            sqlite3_bind_text(stmt, 1, [str1 UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(stmt, 2, [str2 UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(stmt, 3, [str3 UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(stmt, 4, [str4 UTF8String], -1, SQLITE_TRANSIENT);

            success = sqlite3_step(stmt);
        }
        sqlite3_finalize(stmt);
    }
    else {
        sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
}


-(NSMutableArray *)getCarSectionDetails{
	
    NSMutableArray *arrdetails = [[[NSMutableArray alloc]init]autorelease];

    if(sqlite3_open([[self GetDBPath] UTF8String], &database) == SQLITE_OK) {
		NSLog(@"%@",path);
        const char *sql = "select * from NewCarSection";
        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
           

             while (sqlite3_step(statement) == SQLITE_ROW) {
                 
        NSMutableDictionary *dict = [[NSMutableDictionary alloc] init];
	
      if([[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)] length]>0)
        [dict setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)] forKey:@"carsection"];
                 NSLog(@"%@",[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)]);
      
                 if([[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)] length]>0)
        [dict setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)] forKey:@"accidentdamage"];		
                 NSLog(@"%@",[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)]);

      if([[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)] length]>0)
      [dict setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)] forKey:@"nonrelatedaccident"];
                 NSLog(@"%@",[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)]);
      
      if([[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)] length]>0)
      [dict setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)] forKey:@"privatequotearea"];   
                 NSLog(@"%@",[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)]);

                 [arrdetails addObject:dict];
                 NSLog(@"%@",arrdetails);

                  [dict release];  
             }
            //[dict release]; 
        }     
		
        else {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
		}
        
        sqlite3_finalize(statement);
    } else {
        sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
	return arrdetails;
}




-(NSMutableDictionary *)oneCarSectionDetail:(NSString*)sectionTitle {
	
    NSMutableDictionary *dict = [[[NSMutableDictionary alloc] init]autorelease];

    if(sqlite3_open([[self GetDBPath] UTF8String], &database) == SQLITE_OK) {
		NSLog(@"%@",path);
		const char *sql = "select * from NewCarSection where carsection = ? ";
        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
            
            sqlite3_bind_text(statement, 1, [sectionTitle UTF8String], -1, SQLITE_TRANSIENT);
            
            // while (sqlite3_step(statement) == SQLITE_ROW) {
            
            if([[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)] length]>0)
                [dict setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)] forKey:@"carsection"];
            
            if([[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)] length]>0)
                [dict setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)] forKey:@"accidentdamage"];		
            if([[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)] length]>0)
                [dict setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)] forKey:@"nonrelatedaccident"];
            
            if([[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)] length]>0)
                [dict setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)] forKey:@"privatequotearea"];      
        }
		else {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
		}
        
        sqlite3_finalize(statement);
    } else {
        sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    }
	return dict;
}



-(void)updateCarSection:(NSString*)carSection  accidentDamage:(NSString*)damage nonRelatedAccident:(NSString*)nonrelated  privateQArea:(NSString*)qArea {
	//int paraid = 0;
    NSString *str1 = [[NSString alloc] initWithString:carSection];
    NSString *str2 = [[NSString alloc] initWithString:damage];
    
    NSString *str3 = [[NSString alloc] initWithString:nonrelated];
    NSString *str4 = [[NSString alloc] initWithString:qArea];
    
    if (addStmt == nil) {
		const char *sql = "UPDATE NewCarSection SET accidentdamage = ? ,nonrelatedaccident = ? ,privatequotearea = ?  Where carsection = ?";
		if (sqlite3_prepare_v2(database, sql, -1, &addStmt, NULL) != SQLITE_OK) {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
		}
	}
	sqlite3_bind_text(addStmt, 1, [str2 UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(addStmt, 2, [str3 UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(addStmt, 3, [str4 UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_text(addStmt, 4, [str1 UTF8String], -1, SQLITE_TRANSIENT);

	int success = sqlite3_step(addStmt);
	
	if (success != SQLITE_DONE) {
		NSAssert1(0, @"Error: failed to save priority with message '%s'.", sqlite3_errmsg(database));
	}
	sqlite3_reset(addStmt);
	addStmt = nil;
}


-(void)deleteSection:(NSString *)sectionName {
   
    NSString *str = [[NSString alloc] initWithString:sectionName];
    
    if (deleteStmt == nil) {
		const char *sql = "Delete from NewCarSection where carsection = ?";
		if (sqlite3_prepare_v2(database, sql, -1, &deleteStmt, NULL) != SQLITE_OK) {
			NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
		}
	}
	sqlite3_bind_text(deleteStmt, 1, [str UTF8String], -1, SQLITE_TRANSIENT);
	int success = sqlite3_step(deleteStmt);
	
	if (success != SQLITE_DONE) {
		NSAssert1(0, @"Error: failed to save priority with message '%s'.", sqlite3_errmsg(database));
	}
	sqlite3_reset(deleteStmt);
}

 


@end
